<?php
require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\IOFactory;

class ExcelProcessor {
    private $spreadsheet;
    private $firstSheet;
    private $investmentRules;
    
    public function __construct($filePath) {
        $this->spreadsheet = IOFactory::load($filePath);
        $this->firstSheet = $this->spreadsheet->getSheet(0);
    } 
    //读取sheet表格数据   
    public function processSheets() {
        $columnA = $this->firstSheet->rangeToArray('A2:A' . $this->firstSheet->getHighestRow());
        $results = [];        
        foreach ($columnA as $row){
            $sheetName = $row[0];
            if ($this->spreadsheet->sheetNameExists($sheetName)) {
                $targetSheet = $this->spreadsheet->getSheetByName($sheetName);
                $results[$sheetName] = $targetSheet->toArray();
            }
        }
        return $results;
    }
    /**
     * 设置投资规则
     * 将价格区间分为4档，每档投资金额为基础金额的2^(4-n)倍
     */
    public function setupInvestmentRules($index=2)
    {
        //从sheet1中读取最大价格和最小价格
        $maxPrice = $this->firstSheet->getCell('C'.$index)->getValue();
        $minPrice = $this->firstSheet->getCell('D'.$index)->getValue();
        $baseAmount = $this->firstSheet->getCell('E'.$index)->getValue();
        $date = $this->firstSheet->getCell('J'.$index)->getValue();
        $code = $this->firstSheet->getCell('A'.$index)->getValue();
        $name = $this->firstSheet->getCell('B'.$index)->getValue();
        $priceRange = $maxPrice - $minPrice;
        $step = $priceRange / 4;
        $currentMax = $maxPrice;
        $investmentRules = [];
        for ($i = 4; $i > 0; $i--) {
            $currentMin = $currentMax - $step;
            $amount = $baseAmount * pow(2, (4 - $i));
            $investmentRules[] = [
                'min' => round($currentMin,4),
                'max' => round($currentMax,4),
                'amount' => $amount
            ];
            $currentMax = $currentMin;
        }
        $this->investmentRules = $investmentRules;
        $this->calculateSellPoint($code,$name, $date, $index);
    }
    //每轮从开始定投到最后一天的最低价作为参考计算下一轮上涨至参考最低价的115%时为卖点，
    /**
     * 计算卖出点
     * @param string $code ETF代码
     * @param string $startDate 开始定投日期
     * @param int $index 行号
     * @return array 返回包含卖出点和相关信息的数组
     */
    public function calculateSellPoint($code,$name, $startDate,$index=1)
    {
        //接收code，查询对应sheet，获取从指定日期开始后的最小价格
        $sheetName = $code;
        if (!$this->spreadsheet->sheetNameExists($sheetName)) {
            return ['error' => "Sheet '$sheetName' not found."];
        }
        $targetSheet = $this->spreadsheet->getSheetByName($sheetName);
        $highestRow = $targetSheet->getHighestRow();
        $min = 100; // 初始化最小价格为无穷大
        $first = 0;
        $startDate = date('Y-m-d', strtotime($startDate)); // 确保日期格式为 Y-m-d       
        $cost = 0;
        $costNum = 0; 
        for ($row = 3; $row <= $highestRow; $row++) {            
            $dateValue = $targetSheet->getCell('A'. $row)->getValue();
            $dayprice = $targetSheet->getCell('B'. $row)->getValue();
            if (empty($dayprice)) {
                continue; // 跳过空单元格
            }
            $price = $dayprice;
            if (is_numeric($dateValue)) {
                // 转换Excel日期格式为Y-m-d
                $date = \DateTime::createFromFormat('U', ($dateValue - 25569) * 86400);
                $date = $date? $date->format('Y-m-d') : $dateValue;
            } else {
                $date = $dateValue;
            }
            if ($date == $startDate) {
                $first = $min = $price;
            }elseif ($date >= $startDate) {
                if ($price < $min) {
                    $min = $price;
                }
            }
            //核算成本
            $is_sell = $targetSheet->getCell('H'. $row)->getValue();
            if($is_sell!='1' && $date >= $startDate){
                $cost += $targetSheet->getCell('D'.$row)->getValue();
                $costNum += $targetSheet->getCell('E'.$row)->getCalculatedValue();
            }
        }
        //查询当前需要定投的金额，循环$this->investmentRules查找当前价格sellPoint时需要定投的金额
        $touPrice = 0;
        $qu['min'] = $qu['max'] = $qu['amount'] = 0;
        foreach ($this->investmentRules as $rule) {
            if ($rule['min'] <= $price && $rule['max'] >= $price) {
                $touPrice = $rule['amount'];
                $qu['min'] = $rule['min'];
                $qu['max'] = $rule['max'];
                break;
            }
        }
        //计算成本价
        $costPrice = $costNum>0?number_format($cost/$costNum,2):0;
        $runPrice = number_format($costNum * $price - $cost,2);
        $runRat = $cost>0?number_format(100*$runPrice/$cost,2):0;
        // 计算参考最低价（本轮定投期间的最低价格）
        $minPrice = min($min, $first);
        // 计算卖出点（参考最低价的115%）
        $sellPoint = $minPrice * 1.15; 
        //涨幅
        $rise = $minPrice>0?number_format(($price-$minPrice)*100/$minPrice,2).'%':'0%';
        $rise2 = $first>0?number_format(($price-$first)*100/$first,2).'%':'0%'; //从开始定投到最后一天的涨幅
        echo "编号：{$code} - 名称：{$name} - 当前：".number_format($price,4)." - 浮动涨幅：".$rise." - 涨幅：".$rise2." - 成本：".$costPrice." - 利润：".$runPrice." - 收益比例：".$runRat."% - 累计：".$cost." - 份额：".number_format($costNum,0)." - ".($price>$sellPoint?'(卖出)':'  ')." - 最低" . number_format($minPrice, 4) . "\n";
    }
}

// 使用示例
$processor = new ExcelProcessor('xlsx/database.xlsx');
for($i=2;$i<=13;$i++){
    $processor->setupInvestmentRules($i); 
}
echo PHP_EOL;
$processor2 = new ExcelProcessor('xlsx/database2.xlsx');
for($i=2;$i<=5;$i++){
    $processor2->setupInvestmentRules($i); 
}
// 处理后的数据可以使用 $processedData  
?>